1.Introduction to the database 

1.1 MySQL

MySQL claims to be the most popular open source database, and it is one of the most popular RDBMS (Relational Database Management System) applications.The M in LAMP refers to MySQL.Applications built on LAMP use MySQL.

MySQL was originally developed by MySQL AB, then sold in 2008 for $1 billion to Sun, which in turn was acquired by Oracle in 2010.The Oracle acquisition led to the creation of two versions of MySQL: a commercial version and a community version. For the latter, Oracle has been criticized by a wide range of users due to its control of MySQL development.

1.2 PostgreSQL

PostgreSQL boasts to be the world's most advanced open source database, belonging to the relational database management system (ORDBMS), which is based on the object-relational database management system developed by the University of California's Department of Computer Science, POSTGRES, version 4.2, which was originally developed at the University of California, Berkeley in 1985 as a successor to the Ingres database. PostgreSQL is a completely community-driven open source project.

It offers a single, fully functional version, unlike MySQL, which offers several different community, commercial, and enterprise editions.PostgreSQL is based on a free BSD/MIT license, which allows organizations to use, copy, modify, and redistribute the code, simply by providing a copyright notice.

2. Performance Comparison

test environment

MySQL：      
    - 硬件配置： 4核心 16GB内存    
    - 版本：  MySQL 8.0
Postgres SQL：     
    - 硬件配置： 4核心 16GB内存    
    - 版本：Postgres SQL 13

The pressure test data, SELECT are SELECT in accordance with the primary key query, UPDATE in accordance with the primary key for UPDATE, INSERT is an INSERT line of data. From the stress test data, we can draw the following conclusions:

1. In terms of throughput, Postgres SQL outperforms MySQL by a factor of 2 for SELECT, by a factor of 4-5 for INSERT, and by a factor of 5-6 for UPDATE.

2. On average, Postgres SQL outperforms MySQL by more than a few times.

3. Especially in terms of hot row updates, MySQL's performance is only about 1/8th of Postgres SQL's, and it takes 7 times longer!

3. Applicable scenarios, how to choose?

Relative to Postgres MySQL is simpler, so there is a higher popularity, in the technical data, as well as technical component support, support is also more complete, but does not mean that it is not irreplaceable, in the author's case, MYSQL is more like for small and medium-sized enterprises, individuals, a database tool, because there will be more people, the documentation is more complete, so it is difficult to get started. But these do not mean that MYSQL is the best.

From the above chart Postgres SQL's development momentum is very rapid, and currently has a hidden trend of catching up with MySQL, while MySQL relatively speaking, the use of popularity has been showing a downward trend.

Scenarios for MySQL

MySQL is suitable for simple applications such as e-commerce, blogs, websites, etc. It can be used by small, medium, and large systems, and can support data volumes from tens of millions to hundreds of millions.

However, in high-performance scenarios, such as faster response and higher throughput, MYSQL's performance is slightly stretched.

In addition, when the query conditions are more complex, the business throughput requirements are not high, and the response time is not required, you can choose MYSQL.

Scenarios for Postgres SQL

In general, Postgre SQL is more suitable for complex data structures, advanced applications and large-scale datasets, of course, if the data size is relatively small, you can also choose Postgres SQL, no matter what the scenario, if you want to use Postgres SQL, you can always find the corresponding solution.

Yes and only, in the query conditions are more complex, the time is not quite applicable, because according to our actual online business performance is Postgre SQL may choose the wrong index.

4. Summarize

Advantages of PostgreSQL over MySQL

• Postgre SQL is far better than MYSQL in terms of performance, as shown by the above stress test data, both in terms of time consumption and overall throughput.

• Postgre SQL has a significant advantage in single row updates, especially with HOT UPDATE enabled, which is an order of magnitude higher than MYSQL.

• The standard implementation of SQL is better than MySQL, and the functional implementation is more rigorous and collegiate;

• Postgre SQL uses a heap table to store the main table, while MySQL uses indexes to organize the table, and can support larger data volumes than MySQL.

• Postgre SQL's master/backup replication is physical replication. Compared to MySQL's binlog-based logical replication, data consistency is more reliable, replication performance is higher, and the impact on host performance is smaller.

• MySQL's transaction isolation level repeatable read can't prevent common concurrent updates, you have to add locks, but pessimistic locks will affect performance, and it's complicated to implement optimistic locks manually. Postgre SQL has a hidden optimistic lock version field in the columns, and the default repeatable read level guarantees the correctness of concurrent updates with the performance of optimistic locks.

Comparative Disadvantages of Postgre SQL over MySQL:

• The table design of Postgre SQL is relatively complex, which makes it more complicated to perform some statistics and operations on the system tables.

• Postgre SQL's index selection has a higher probability of selecting the wrong index (real test), and it can't use force_index as easily as mysql.

• Postgre SQL has a vacuum, you need to adjust the vacuum parameter according to the specific usage scenario.